﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using myomscl.Utils;

namespace myomscl.DataObjects
{
    public class AlertViewDAL : SqlBaseDal
    {
        public EstateDataSet.AlertDetailsDataTable GetAlertDetails(WorkflowFilterSql filterSql)
        {
            var result = new EstateDataSet.AlertDetailsDataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Alert_GetDetails;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.AlertId;

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        public EstateDataSet.SummaryDetailsDataTable GetAlertSummaryDetails(WorkflowFilterSql filterSql)
        {
            var result = new EstateDataSet.SummaryDetailsDataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Alert_GetSummaryDetails;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.AlertId;

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        public EstateDataSet.UsedMaterialsDataTable GetAlertMaterials(WorkflowFilterSql filterSql)
        {
            var result = new EstateDataSet.UsedMaterialsDataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Materials_GetListForAlert;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.AlertId;

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        public DataTable GetContractors(WorkflowFilterSql filterSql)
        {
            var result = new DataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Alert_GetContractors;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.AlertId;

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        public void UpdateAlertActualData(WorkflowFilterSql filterSql, EstateDataSet.UsedMaterialsDataTable usedMaterialsDataTable)
        {
            using (SqlConnection dbc = GetConnection())
            {
                var cmd = dbc.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    cmd.CommandText = Constants.Sp_Name_Alert_UpdateDetails;
                    cmd.Parameters.Clear();

                    var parameters = new SqlParameter[14];

                    parameters[0] = filterSql.AlertId;
                    parameters[1] = filterSql.NewStatus;
                    parameters[2] = filterSql.OldStatus;
                    parameters[3] = filterSql.EstimateStartDate;
                    parameters[4] = filterSql.EstimateEndDate;
                    parameters[5] = filterSql.AlertArea;
                    parameters[6] = filterSql.StartDate;
                    parameters[7] = filterSql.EndDate;
                    parameters[8] = filterSql.Comments;
                    parameters[9] = filterSql.TrafficProblems;
                    parameters[10] = filterSql.ContractorId;
                    parameters[11] = filterSql.UserId;
                    parameters[12] = filterSql.Archive;
                    parameters[13] = new SqlParameter()
                    {
                        ParameterName = "@actualmaterials",
                        SqlDbType = System.Data.SqlDbType.Structured,
                        Value = usedMaterialsDataTable
                    };

                    cmd.Parameters.AddRange(parameters);

                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        public EstateDataSet.ActualDataDataTable GetActualData(WorkflowFilterSql filterSql)
        {
            var result = new EstateDataSet.ActualDataDataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Alert_GetActualData;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.AlertId;

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        public void PrepareInvoiceData(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                var cmd = dbc.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    cmd.CommandText = Constants.Sp_Name_Invoice_Create;
                    cmd.Parameters.Clear();

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.AlertId;

                    cmd.Parameters.AddRange(parameters);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        public EstateDataSet.RemarksDataTable GetRemarks(WorkflowFilterSql filterSql)
        {
            var result = new EstateDataSet.RemarksDataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Alert_GetRemarks;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.AlertId;

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        internal void AddRemark(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                var cmd = dbc.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    cmd.CommandText = Constants.Sp_Name_Alert_AddRemark;
                    cmd.Parameters.Clear();

                    var parameters = new SqlParameter[3];
                    parameters[0] = filterSql.AlertId;
                    parameters[1] = filterSql.Comments;
                    parameters[2] = filterSql.UserId;

                    cmd.Parameters.AddRange(parameters);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        internal void UpdateContractor(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                var cmd = dbc.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    cmd.CommandText = Constants.Sp_Name_Alert_UpdateContractor;
                    cmd.Parameters.Clear();

                    var parameters = new SqlParameter[2];
                    parameters[0] = filterSql.AlertId;
                    parameters[1] = filterSql.ContractorId;

                    cmd.Parameters.AddRange(parameters);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        internal void DeleteMaterial(int alertId, string materialId)
        {
            using (SqlConnection dbc = GetConnection())
            {
                var cmd = dbc.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format("DELETE FROM AlertMaterials " +
                    "WHERE alertId={0} AND materialId={1}", alertId, materialId);
                try
                {
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        internal void UpdateMaterial(int alertId, string materialId, string quantity)
        {
            using (SqlConnection dbc = GetConnection())
            {
                var cmd = dbc.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format("UPDATE AlertMaterials " +
                    "SET quantity = {2} " +
                    "WHERE alertId={0} AND materialId={1}", alertId, materialId, quantity);
                try
                {
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        internal void InsertMaterial(int alertId, string materialId, string quantity)
        {
            using (SqlConnection dbc = GetConnection())
            {
                var cmd = dbc.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format("INSERT INTO [AlertMaterials]([alertId],[materialId],[quantity],[price]) " +
                    "VALUES({0},{1},{2},db_owner.SetMaterialPrice({0},{1}))", alertId, materialId, quantity);

                try
                {
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        internal void UpdateRemarks(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                var cmd = dbc.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    cmd.CommandText = Constants.Sp_Name_Alert_UpdateRemark;
                    cmd.Parameters.Clear();

                    var parameters = new SqlParameter[2];
                    parameters[0] = filterSql.Id;
                    parameters[1] = filterSql.Comments;

                    cmd.Parameters.AddRange(parameters);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }
    }
}
